Use RD4_DWCR
go

create PROCEDURE ShowInfor
(@Branch VARCHAR(10)='ALL',

@InforType VARCHAR(10)='Salary'
)
as
	IF (@Branch = 'ALL' and @Infortype='Salary')
	Begin	
		SELECT ROW_NUMBER() OVER (
				ORDER BY [RateSalaryAnnual] DESC
				) AS [Row],
			NTILE(100) OVER (
				ORDER BY [RateSalaryAnnual] DESC
				) AS [ Rank],
			b.AlphaName,
			a.BUSINESSUNITCODE,
			a.JobType,
			RateSalaryAnnual,
			a.category01payroll,
			CONVERT(DATE, DateBirth) AS [DOB],
			CONVERT(DATE, [DateOriginalEmployment]) AS [Started date],
			CONVERT(DATE, [DateLastRaise]) AS [Date Last Raise],
			RatePreviousSalary
		FROM JDEPYEmployeeMasterInfo a,
			JDEABAddressBookMaster b
		WHERE b.[AddressNumber] = a.AddressNumber
			AND b.SearchTypeCode = 'E'
			--AND a.BusinessUnitCode = '         782'
		ORDER BY RateSalaryAnnual DESC
	END
	Else IF (@Branch <> 'IT' and @InforType='Salary')
	Begin
		SELECT ROW_NUMBER() OVER (
				ORDER BY [RateSalaryAnnual] DESC
				) AS [Row],
			NTILE(100) OVER (
				ORDER BY [RateSalaryAnnual] DESC
				) AS [ Rank],
			b.AlphaName,
			a.BUSINESSUNITCODE,
			a.JobType,
			RateSalaryAnnual,
			a.category01payroll,
			CONVERT(DATE, DateBirth) AS [DOB],
			CONVERT(DATE, [DateOriginalEmployment]) AS [Started date],
			CONVERT(DATE, [DateLastRaise]) AS [Date Last Raise],
			RatePreviousSalary
		FROM JDEPYEmployeeMasterInfo a,
			JDEABAddressBookMaster b
		WHERE b.[AddressNumber] = a.AddressNumber
			AND b.SearchTypeCode = 'E'
			AND a.BusinessUnitCode = '         782'
		ORDER BY RateSalaryAnnual DESC
	END

